package itsm.isperp.framework.data.excel;

import itsm.isperp.framework.core.context.ContextHolder;
import itsm.isperp.framework.data.excel.entity.ExcelDataType;
import itsm.isperp.framework.data.excel.executor.ExcelDataReader;
import itsm.isperp.framework.data.excel.executor.ParseXMLExecutor;
import itsm.isperp.framework.data.excel.validate.DateValidatator;
import itsm.isperp.framework.data.excel.validate.LengthValidator;
import itsm.isperp.framework.data.excel.validate.NotNullValidator;
import itsm.isperp.framework.data.excel.validate.NumberValidator;
import itsm.isperp.framework.data.excel.validate.UniqueValidator;
import itsm.isperp.module.entity.db.DbEntityField;
import itsm.isperp.module.service.configuration.ConfCategoryService;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;

/**
 * 读取导入的Excel的内容
 */
public class ExcelImportExecutor {

	/**
	 * 根据一个以Cell位置为key，DbEntityField为值的Map导入读取指定文件以及进行数据校验并返回一个ExcelData的对象
	 * 
	 * @param map
	 *            一个以Cell位置为key，DbEntityField为值的Map
	 * @param fileName
	 *            excel文件
	 * @return Excel中需要导入的数据
	 * @throws ExcelImportException
	 */
	public static ExcelData readExcel(Map<String, DbEntityField> fields,
			String fileName, String sheetName) throws ExcelImportException {

		FileInputStream importExcelStream;
		Workbook wb;
		try {
			importExcelStream = new FileInputStream(fileName);

			wb = new HSSFWorkbook(importExcelStream);

		} catch (FileNotFoundException e) {
			throw new ExcelImportException("导入Excel失败 - 文件未找到 : ", e);
		} catch (IOException e) {
			throw new ExcelImportException("导入Excel失败 - 错误在Excel文件 : ", e);
		}
		// 读取excel

		Sheet sheet = wb.getSheet(sheetName);
		ExcelStruct excelStruct = getExcelStruce(fields, sheet);
		return readExcel(excelStruct, sheet);

	}
	
	public static String checkIfContainCategoryPy(int baseSize,
			String fileName, String sheetName) throws ExcelImportException {

		FileInputStream importExcelStream;
		Workbook wb;
		try {
			importExcelStream = new FileInputStream(fileName);
			wb = new HSSFWorkbook(importExcelStream);
		} catch (FileNotFoundException e) {
			throw new ExcelImportException("导入Excel失败 - 文件未找到 : ", e);
		} catch (IOException e) {
			throw new ExcelImportException("导入Excel失败 - 错误在Excel文件 : ", e);
		}
		// 读取excel
		boolean ifContainColumn=false;
		boolean ifSameCategory=true;
		String categoryId="";
		Sheet sheet = wb.getSheet(sheetName);
		Row row = sheet.getRow(sheet.getFirstRowNum());
		int baseColumnSize=baseSize;
		for (int j = baseColumnSize; j < row.getPhysicalNumberOfCells(); j++) {
			String cellValue = row.getCell(j).getStringCellValue();
			if (StringUtils.isNotEmpty(cellValue)) {
				ifContainColumn=true;
				break;
			}
		}
		String categoryName=sheet.getRow(1).getCell(3).getStringCellValue();
		
		/*需要所有的资产都是同一个分类，保证扩展属性导入无误
		 * for (int i = 2; i <= sheet.getLastRowNum(); i++) {
			Row srow = sheet.getRow(i);
			String value=srow.getCell(3).getStringCellValue();
			if(!categoryName.equals(value))
				ifSameCategory=false;
		}*/
		if(ifSameCategory && ifContainColumn)
		{
			ConfCategoryService confCategoryService = ContextHolder.getSpringBean("confCategoryService");
			categoryId=confCategoryService.getCategoryIdByName(categoryName);
		}
		return categoryId;
	}
	
	

	/**
	 * 根据一个以Cell位置为key，DbEntityField为值的Map导入读取指定文件以及进行数据校验并返回一个ExcelData的对象
	 * 
	 * @param map
	 *            一个以Cell位置为key，DbEntityField为值的Map
	 * @param fileName
	 *            excel文件
	 * @return Excel中需要导入的数据
	 * @throws ExcelImportException
	 */
	// public static ExcelData readExcel(Map<String, DbEntityField> map,
	// String fileName, int sheetIndex) throws ExcelImportException {
	//
	// ExcelStruct excelStruct = getExcelStruce(map);
	// FileInputStream importExcelStream;
	// try {
	// importExcelStream = new FileInputStream(fileName);
	// } catch (FileNotFoundException e) {
	// throw new ExcelImportException("导入Excel失败 - 文件未找到 : ", e);
	// }
	//
	// return readExcel(excelStruct, importExcelStream, sheetIndex);
	//
	// }

	/**
	 * 读取导入的Excel的文件内容
	 * 
	 * @param xmlFile
	 *            描述被导入的Excel的格式的XML文件
	 * @param importExcelStream
	 *            被导入的XML文件
	 * @return Excel中需要导入的数据
	 */
	public static ExcelData readExcel(String xml, InputStream importExcelStream)
			throws ExcelImportException {
		if (xml == null || importExcelStream == null) {
			return null;
		}
		// 1. 解析XML描述文件
		ExcelStruct excelStruct;
		try {
			excelStruct = ParseXMLExecutor.parseImportStruct(xml);
		} catch (Exception e) {
			// log.error("导入Excel失败 - XML描述文件未找到 : ", e);
			throw new ExcelImportException("导入Excel失败 - XML描述文件未找到 : ", e);
		}
		// 2. 按照XML描述文件，来解析Excel中文件的内容
		return readExcel(excelStruct, importExcelStream, 0);

	};

	private static ExcelStruct getExcelStruce(Map<String, DbEntityField> map,
			Sheet sheet) {
		Set<String> keys = map.keySet();

		List<ImportCellDesc> repeatImportCells = new ArrayList<ImportCellDesc>();
		for (String str : keys) {
			DbEntityField field = map.get(str);

			if (field == null) {
				continue;
			}
			ImportCellDesc cellDesc = new ImportCellDesc();
			int firstRow = sheet.getFirstRowNum();
			Row row = sheet.getRow(sheet.getFirstRowNum());
			for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
				String cellValue = row.getCell(j).getStringCellValue();
				if (StringUtils.isNotEmpty(cellValue) && cellValue.equals(str)) {
					
					CellReference ref = new CellReference(firstRow + 1, j);
					String frfStr=ref.formatAsString();
					if(StringUtils.isNotEmpty(frfStr))
					cellDesc.setCellRef(frfStr);
					break;
				}
			}

			cellDesc.setFieldName(field.getColumnName());

			// 对表格中数据进行唯一性校验－－具有性能损耗
			if (field.isUniqueKey()) {
				cellDesc.addValidator(UniqueValidator.class.getName());
			}
			if (field.isNotnull()) {
				cellDesc.addValidator(NotNullValidator.class.getName());
			}
			String filedType = field.getFieldType();

			if ("number".equals(filedType)) {
				cellDesc.addValidator(NumberValidator.class.getName());
				cellDesc.setDateType(ExcelDataType.number);
			} else if ("date".equals(filedType)) {

				cellDesc.setDateType(ExcelDataType.date);
				cellDesc.addValidator(DateValidatator.class.getName());
			} else {
				cellDesc.setDateType(ExcelDataType.string);
			}

			int maxLen = field.getMaxLength();
			if (maxLen > 0) {
				cellDesc.addValidator(LengthValidator.class.getName());
				cellDesc.putParam("maxLength", maxLen);
				cellDesc.putParam("minLength", field.getMinLength());
			}
			
			if(StringUtils.isNotEmpty(cellDesc.getCellRef()))
			    repeatImportCells.add(cellDesc);
		}
		return new ExcelStruct(repeatImportCells, "End");
	};

	private static ExcelData readExcel(ExcelStruct excelStruct, Sheet sheet)
			throws ExcelImportException {
		try {
			return ExcelDataReader.readExcel(excelStruct, sheet);
		} catch (IOException e) {
			// log.error("导入Excel失败 - IO异常 : ", e);
			throw new ExcelImportException("导入Excel失败 - IO异常 : ", e);
		} catch (Exception e) {
			// log.error("导入Excel失败 : ", e);
			throw new ExcelImportException("导入Excel失败 : ", e);
		}

	};

	private static ExcelData readExcel(ExcelStruct excelStruct,
			InputStream importExcelStream, int sheetIndex)
			throws ExcelImportException {
		try {
			return ExcelDataReader.readExcel(excelStruct, importExcelStream,
					sheetIndex);
		} catch (IOException e) {
			// log.error("导入Excel失败 - IO异常 : ", e);
			throw new ExcelImportException("导入Excel失败 - IO异常 : ", e);
		} catch (Exception e) {
			// log.error("导入Excel失败 : ", e);
			throw new ExcelImportException("导入Excel失败 : ", e);
		}

	};

}